﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace EarringsBackEnd
{
    public class MasterDataManagement
    {
        private volatile static MasterDataManagement singleTonObject;
        private static object lockingObject = new object();

        //About Earrings Config
        private MasterDataManagement()
        {

        }

        public static MasterDataManagement InstanceCreation()
        {            
            if (singleTonObject == null)
            {
                lock (lockingObject)
                {
                    if (singleTonObject == null)
                    {
                        singleTonObject = new  MasterDataManagement();
                    }
                }
            }
            return singleTonObject;
        }

        public DataTable GetMasterData(SqlParameter[] sqlParams)
        {
            StringBuilder sqlStr = new StringBuilder();
            DataTable dt = null;

            sqlStr.Append("SELECT ROW_NUMBER()OVER(ORDER BY Config_id ) AS [No],");
            sqlStr.Append(" Config_id, Config_NameTh, Config_Name,Short_Name ");
            sqlStr.Append(" FROM EarringsConfig WHERE Config_Type = @Type");
            dt = new DataTable();
            dt = SqlClientUtility.ExecuteDataTable(CommandType.Text, sqlStr.ToString(),sqlParams);



            return dt;
        }

        public void SaveMasterData(ConfigType confType, SqlParameter[] sqlParams)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("INSERT INTO EarringsConfig");
            sqlStr.Append(" (Config_Type, Config_TypeTh, Config_Name, Config_NameTh");
            sqlStr.Append(" ,Config_Group,Short_Name,Short_Type,Remark)");
            sqlStr.Append(" VALUES (@Type,@TypeTh,@Name,@NameTh,@GroupT,");
            sqlStr.Append(" @ShortN,@ShortT,@Remark)");
            SqlClientUtility.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), sqlParams);
        }

        public void UpdateMasterData(ConfigType confType,SqlParameter[] sqlParams)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("UPDATE EarringsConfig");
            sqlStr.Append(" SET Config_Name = @Name");
            sqlStr.Append(" , Config_NameTh = @NameTh");
            sqlStr.Append(" , Remark = @Remark");
            sqlStr.Append(" WHERE Config_Id = @KeyId");
            sqlStr.Append(" AND Config_Type = @Type");
            SqlClientUtility.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), sqlParams);
        }

        public void DeleteMasterData(SqlParameter[] sqlParams)
        {
            string sqlStr = string.Empty;
            sqlStr = "DELETE FROM EarringsConfig WHERE Config_Id = @KeyId";
            SqlClientUtility.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), sqlParams);            
        }

        public string GetMaxKey(string type)
        {
            string sqlStr = null;
            string maxKey = null;
            SqlParameter[] sqlParams = new SqlParameter[] 
                {
                    new SqlParameter("@Type", type) ,
                };

            sqlStr = "SELECT MAX(Config_Id) FROM EarringsConfig WHERE Config_Type = @Type";
            maxKey = SqlClientUtility.ExecuteScalar(CommandType.Text, sqlStr,sqlParams).ToString();

            return maxKey;
        }

        
        //About Earrings Suitability

        public List<T> GetProperties<T>(string grpName) where T : class
        {
            string sqlStr = string.Empty;
            SqlParameter[] sqlParams = new SqlParameter[] 
                {
                    new SqlParameter("@grpName", grpName)
                };
            sqlStr = "SELECT DISTINCT Short_Type, Config_TypeTh FROM EarringsConfig WHERE Config_Group = @grpName";
            SqlDataReader reader = SqlClientUtility.ExecuteReader(CommandType.Text, sqlStr, sqlParams);


            Type classType = typeof(T);
            var listType = typeof(List<>);
            var construct = listType.MakeGenericType(classType);
            List<T> listModel = Activator.CreateInstance(construct) as List<T>;

            while (reader.Read())
            {
                T propObj = (T)Activator.CreateInstance(classType);

                PropertyInfo propInfo = classType.GetProperty("KeyId");
                propInfo.SetValue(propObj, SqlClientUtility.GetString(reader, "Short_Type", ""), null);

                propInfo = classType.GetProperty("Name");
                propInfo.SetValue(propObj, SqlClientUtility.GetString(reader, "Config_TypeTh", ""), null);
                
                listModel.Add(propObj);
            }

            reader.Close();

            return listModel;
        }

        public List<T> GetPropertiesDetail<T>(string type) where T : class
        {
            string sqlStr = string.Empty;
            SqlParameter[] sqlParams = new SqlParameter[] 
                {
                    new SqlParameter("@Type", type)
                };
            sqlStr = "SELECT Short_Name, Config_NameTh,Config_Id FROM EarringsConfig WHERE Config_TypeTh =  @Type";
            SqlDataReader reader = SqlClientUtility.ExecuteReader(CommandType.Text, sqlStr, sqlParams);

            Type classType = typeof(T);
            var listType = typeof(List<>);
            var construct = listType.MakeGenericType(classType);
            List<T> listModel = Activator.CreateInstance(construct) as List<T>;

            while (reader.Read())
            {
                T propObj = (T)Activator.CreateInstance(classType);

                PropertyInfo propInfo = classType.GetProperty("KeyId");
                propInfo.SetValue(propObj, SqlClientUtility.GetString(reader, "Short_Name", ""), null);

                propInfo = classType.GetProperty("Name");
                propInfo.SetValue(propObj, SqlClientUtility.GetString(reader, "Config_NameTh", ""), null);

                propInfo = classType.GetProperty("ConfigId");
                propInfo.SetValue(propObj, SqlClientUtility.GetString(reader, "Config_Id", ""), null);

                listModel.Add(propObj);
            }

            reader.Close();

            return listModel;
        }
        
        public DataTable GetEarringsSuitability(SqlParameter[] sqlParams)
        {
            StringBuilder sqlStr = new StringBuilder();
            DataTable dt = null;

            sqlStr.Append("SELECT ROW_NUMBER()OVER(ORDER BY t1.suit_id) AS [No], t1.Config_NameTh, t2.Config_NameTh ,t1.Config_Id,t2.Config_Id, t2.Rating ");
            sqlStr.Append(" FROM");
            sqlStr.Append(" (SELECT suit_id, erConf.Config_Id, erConf.Config_NameTh FROM EarringsSuitability erSuit");
            sqlStr.Append(" INNER JOIN EarringsConfig erConf ON erSuit.config_Id1 = erConf.Config_Id");
            sqlStr.Append(" WHERE Short_Type1 = @Conf_Grp1 AND Short_Type2 = @Conf_Grp2) t1");
            sqlStr.Append(" INNER JOIN ");
            sqlStr.Append(" (SELECT suit_id, erConf.Config_Id, erConf.Config_NameTh, erSuit.Rating FROM EarringsSuitability erSuit");
            sqlStr.Append(" INNER JOIN EarringsConfig erConf ON erSuit.config_Id2 = erConf.Config_Id");
            sqlStr.Append(" WHERE Short_Type1 = @Conf_Grp1 AND Short_Type2 = @Conf_Grp2) t2"); 
            sqlStr.Append(" ON t1.suit_id = t2.suit_id");
            sqlStr.Append(" ORDER BY t1.suit_id, t2.suit_id");

            
            dt = new DataTable();
            dt = SqlClientUtility.ExecuteDataTable(CommandType.Text, sqlStr.ToString(), sqlParams);

            return dt;
        }

        public void SaveEarringsSuitability(SqlParameter[] sqlParams)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("INSERT INTO EarringsSuitability");
            sqlStr.Append(" (Suit_Code, Config_Id1, Config_Id2, Rating, Short_Type1, Short_Type2)");
            sqlStr.Append(" VALUES (@SuitCode,@Config_1,@Config_2,@Rating, @Short_Type1, @Short_Type2)");
            SqlClientUtility.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), sqlParams);
        }

        public void UpdateEarringsSuitability(SqlParameter[] sqlParams)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("UPDATE EarringsSuitability");
            sqlStr.Append(" SET Rating = @Rating");
            sqlStr.Append(" WHERE Config_Id1 = @Config_1");
            sqlStr.Append(" AND Config_Id2 = @Config_2");
            sqlStr.Append(" AND Short_Type1 = @Short_Type1");
            sqlStr.Append(" AND Short_Type2 = @Short_Type2");
            SqlClientUtility.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), sqlParams);
        }

        public DataTable SearchEarring (SqlParameter[] sqlParams)
        {
            StringBuilder sqlStr = new StringBuilder();
            DataTable dt = null;

            sqlStr.Append("SELECT * FROM Earrings");

            dt = new DataTable();
            dt = SqlClientUtility.ExecuteDataTable(CommandType.Text, sqlStr.ToString(), sqlParams);

            return dt;
        }

        public List<T> GetMasterEarringConfig<T> (string short_type) where T : class
        {
            string sqlStr = string.Empty;
            SqlParameter[] sqlParams = new SqlParameter[] 
                {
                    new SqlParameter("@Short_Type", short_type)
                };
            sqlStr = "SELECT Config_Id, Config_NameTh FROM EarringsConfig WHERE Short_Type =  @Short_Type";
            SqlDataReader reader = SqlClientUtility.ExecuteReader(CommandType.Text, sqlStr, sqlParams);

            Type classType = typeof(T);
            var listType = typeof(List<>);
            var construct = listType.MakeGenericType(classType);
            List<T> listModel = Activator.CreateInstance(construct) as List<T>;

            while (reader.Read())
            {
                T propObj = (T)Activator.CreateInstance(classType);

                PropertyInfo propInfo = classType.GetProperty("KeyId");
                propInfo.SetValue(propObj, SqlClientUtility.GetString(reader, "Config_Id", ""), null);

                propInfo = classType.GetProperty("Name");
                propInfo.SetValue(propObj, SqlClientUtility.GetString(reader, "Config_NameTh", ""), null);
                
                listModel.Add(propObj);
            }

            reader.Close();

            return listModel;
        }


        //About Earrings Data

        public string GetMaxKeyEarrings(string id)
        {
            string sqlStr = string.Empty;
            string maxKey = string.Empty;

            SqlParameter[] sqlParams = new SqlParameter[] 
                {
                    new SqlParameter("@Id", id) ,
                };

            sqlStr = "SELECT MAX(Er_Code) FROM Earrings WHERE Er_Style=@Id";
            maxKey = SqlClientUtility.ExecuteScalar(CommandType.Text, sqlStr, sqlParams).ToString();

            return maxKey;
        }
        public string GetShortName(SqlParameter[] sqlParams)
        {
            string sqlStr = string.Empty;
            string shortName = string.Empty;

            sqlStr = "SELECT short_name FROM earringsconfig WHERE config_id = @Id";
            shortName = SqlClientUtility.ExecuteScalar(CommandType.Text, sqlStr, sqlParams).ToString();

            return shortName;
        }

        public void SaveEarrings(SqlParameter[] sqlParams)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("INSERT INTO Earrings");
            sqlStr.Append("([Er_Code],[Er_Name],[Er_Style]");
            sqlStr.Append(",[Er_Shape],[Er_Color],[Er_EarBack]");
            sqlStr.Append(",[Er_Width],[Er_Lenght],[Er_Price]");
            sqlStr.Append(",[Er_Quatity],[Er_Remaining],[Er_Detail],[Er_Status]");
            sqlStr.Append(",[Er_ShortName],[Er_Gemstone],[Er_Metal]");
            sqlStr.Append(",[Er_Image_Front],[Er_Image_Side]");
            sqlStr.Append(",[Er_Image_Size],[Er_Image_Sample]");
            sqlStr.Append(",[Er_Image_Left],[Er_Image_Right]");
            sqlStr.Append(",[Er_Back_Left],[Er_Back_Right])");
            sqlStr.Append(" VALUES (@KeyId, @Name,  @StyId");
            sqlStr.Append(" ,@ShpId, @ColId, @ErBId");
            sqlStr.Append(" ,@Width, @Lenght , @Price ");
            sqlStr.Append(" ,@Quatity, @Quatity, @Detail, @Status");
            sqlStr.Append(" ,@ShortName, @Gemstone, @Metal");
            sqlStr.Append(" ,@ImgFront, @ImgSide"); 
            sqlStr.Append(" ,@ImgSize, @ImgSample");
            sqlStr.Append(" ,@ImgLeft, @ImgRight");
            sqlStr.Append(" ,@LeftPosition, @RightPosition)");
            SqlClientUtility.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), sqlParams);
        }

        //About Supplier Data

        public int  SaveSupplier(SqlParameter[] sqlParams)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("INSERT INTO Supplier");
            sqlStr.Append("([SupplierName],[Address]");
            sqlStr.Append(",[ProvinceId],[DistrictId],[SubDistrictId],[Zipcode]");
            sqlStr.Append(",[Telephone],[Fax],[Email])");
            sqlStr.Append(" VALUES ( @Name,  @Address");
            sqlStr.Append(" ,@Province, @District, @SubDistrict,@ZipCode");
            sqlStr.Append(" ,@Telephone , @Fax, @Email);SELECT @@IDENTITY");
            object id = SqlClientUtility.ExecuteScalar(CommandType.Text, sqlStr.ToString(), sqlParams);

            return Convert.ToInt32(id);
        }

        public Dictionary<string, List<Specification>> GetTestData()
        {
            string sql = "Select * from Test";
            //SqlDataReader reader = SqlClientUtility.ExecuteReader(CommandType.Text, sql);

            DataTable dt = SqlClientUtility.ExecuteDataTable(CommandType.Text, sql);            
            
            Dictionary<string, List<Specification>> AllProduct = new Dictionary<string, List<Specification>>() ;

            string[] values = {"F2","F3","F4","F5"};
            List<Specification> attrAll = null;

            foreach (var item in values)
            {
                attrAll = new List<Specification>();
                foreach (DataRow  row in dt.Rows)
                {
                    Specification newAttr = new Specification();
                    newAttr.propertyName = row["Property Name"].ToString();
                    newAttr.value = row[item].ToString();                    
                    attrAll.Add(newAttr);
                }

                AllProduct.Add(item, attrAll);
            }

            //while (reader.Read())
            //{
            //    Specification newAttr = new Specification();
            //    newAttr.propertyName = SqlClientUtility.GetString(reader,"Property Name","");
            //    newAttr.value = SqlClientUtility.GetString(reader, "F2", "");

            //    attrAll = new List<Specification>();
            //    attrAll.Add(newAttr);
            //}

            return AllProduct;
        }
    }

    public class Specification
    {
        public string propertyName { get; set; }
        public string value { get; set; }
    }
}
